package com.lianxi.exacl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.lianxi.pojo.DemoDB;
import com.lianxi.pojo.passport.*;
import com.lianxi.jdbc.DBHelper;
import com.lianxi.model.AreaCode;
import com.lianxi.pojo.SyxUser;
import com.lianxi.util.OkHttpUtils;
import com.lianxi.util.SecurityUtil;
import com.lianxi.util.StringUtil;
import okhttp3.Response;
import org.junit.Test;


import javax.persistence.Column;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

public class MyEasyExcel {

    static String filename = "D:\\Desktop\\syx.xlsx";

    public static void main(String[] args) throws ParseException {

        String filename = "D:\\Desktop\\syx.xlsx";
        List<SyxUser> allNews = getAllNews2();
        System.out.println("数据数量为：" + allNews.size());
        EasyExcel.write(filename, SyxUser.class).sheet("用户信息").doWrite(allNews);
    }


    /**
     * 写入
     *
     * @return
     * @throws ParseException
     */
    public static List<SyxUser> getAllNews() throws ParseException {
        String sql = " SELECT u.`name`,u.`phone`,u.`pwd`FROM  t_user u;";
        List<SyxUser> list = new ArrayList<>();
        Statement stmt = null;
        Connection con = null;
        HashMap<Integer, Integer> map = new HashMap<>();
        String url = null;
        try {
            con = DBHelper.getConnection();
            PreparedStatement ps = con.prepareStatement(sql);
            stmt = (Statement) con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            SimpleDateFormat formattime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            if (rs != null) {
                while (rs.next()) {
                    SyxUser syxUser = new SyxUser();
                    syxUser.setName(rs.getString(1));
                    syxUser.setPhone(SecurityUtil.base64Decode(rs.getString(2)));
                    syxUser.setPassword(SecurityUtil.base64Decode(rs.getString(3)));
                    list.add(syxUser);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.close(con);
        }
        return list;
    }


    @Test
    public void testRead() {
        String filename = "D:\\Desktop\\河南省地区编码表(1)(1).xlsx";
        // 创建ExcelReaderBuilder对象
        ExcelReaderBuilder readerBuilder = EasyExcel.read();
        // 获取文件对象
        readerBuilder.file(filename);
        // 指定映射的数据模板
        //  readerBuilder.head(DemoData.class);
        // 指定sheet
        readerBuilder.sheet(0);
        // 自动关闭输入流
        readerBuilder.autoCloseStream(true);
        // 设置Excel文件格式
        readerBuilder.excelType(ExcelTypeEnum.XLSX);
        // 注册监听器进行数据的解析
        readerBuilder.registerReadListener(new AnalysisEventListener() {
            // 每解析一行数据,该方法会被调用一次
            @Override
            public void invoke(Object demoData, AnalysisContext analysisContext) {
                // 如果没有指定数据模板, 解析的数据会封装成 LinkedHashMap返回
                // demoData instanceof LinkedHashMap 返回 true
                System.out.println("解析数据为:" + demoData.toString());
            }

            // 全部解析完成被调用
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                System.out.println("解析完成...");
                // 可以将解析的数据保存到数据库
            }
        });
        readerBuilder.doReadAll();
          /*  // 构建读取器
            ExcelReader excelReader = readerBuilder.build();
            // 读取Excel
            excelReader.readAll();
            // 关闭流
            excelReader.finish();*/

    }


    /**
     * 关键是写一个监听器,实现AnalysisEventListener,
     * 每解析一行数据会调用invoke方法返回解析的数据,
     * 当全部解析完成后会调用doAfterAllAnalysed方法.
     * 我们重写invoke方法和doAfterAllAnalysed方法即可.
     */

    @Test
    public void testReadExcel() {
        // 读取excel
        EasyExcel.read(filename, SyxUser.class, new AnalysisEventListener<SyxUser>() {
            int count = 0;

            // 每解析一行数据,该方法会被调用一次
            @Override
            public void invoke(SyxUser demoData, AnalysisContext analysisContext) {
                System.out.println("解析数据为:" + demoData.toString() + "计数：" + count++);
            }

            // 全部解析完成被调用
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                System.out.println("解析完成...");
            }
        })
//                .doReadAll();//读取全部sheet
                .sheet().doRead(); //读取单个(第几个)sheet
    }


    public List<AreaCode> testReadAreaCodeExcel() {
        List<AreaCode> lists = new ArrayList<>();
        // 读取excel
        EasyExcel.read("D:\\Desktop\\河南省地区编码表(1)(1).xlsx", AreaCode.class, new AnalysisEventListener<AreaCode>() {
            int count = 0;
            // 每解析一行数据,该方法会被调用一次
            @Override
            public void invoke(AreaCode demoData, AnalysisContext analysisContext) {
                lists.add(demoData);
            }
            // 全部解析完成被调用
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                System.out.println("解析完成...");
            }
        }).sheet().doRead();
        return lists;
    }


    private String cookie = "yfx_c_g_u_id_10000052=_ck22040609210418852366211033553; yfx_c_g_u_id_10000001=_ck22041109344910734577334470554; yfx_key_10000001=; yfx_f_l_v_t_10000001=f_t_1649640889075__r_t_1658888930674__v_t_1658888930674__r_c_9; yfx_f_l_v_t_10000052=f_t_1652683045481__r_t_1659343013488__v_t_1659343013488__r_c_11; sid=5fec01ba-5492-40f5-bfc0-9d9c022533ed";

    /**
     * 向政协cms。导入站点信息
     *
     * @throws IOException
     */
    @Test
    public void siteAdd() throws IOException {
        List<AreaCode> list = testReadAreaCodeExcel();
        List<AreaCode> list1 = list.subList(1, list.size());
        System.out.println("长度为：" + list1.size());
        int count = 0;
        for (AreaCode areaCode : list1) {
            if (areaCode.getHost() != null) {
                HashMap<String, String> map = new HashMap<>();
                map.put("id", areaCode.getCode());
                map.put("siteCode", areaCode.getCode());
                map.put("siteName", areaCode.getXian());
                map.put("description", areaCode.getXian());
                map.put("siteHost", areaCode.getHost());
                map.put("category", "0");
                map.put("groupIds", "2");
                map.put("channelOpenFlag", "1");
                map.put("up", "2");
                map.put("status", "1");
                Response response = OkHttpUtils.newInstance().doPostAndHear("https://cms.hnzx.gov.cn/zw/site/add-site", map, cookie);
                System.out.println(response.body().string() + " 计数：" + count++);
            }
        }
    }


    @Test
    public void test8() {
        testRead();

    }


    /**
     * 根据Bean类的类型,获取数据库字段名称与类型（不太准确，需要核对）
     */
    public void getDBfield() {
        ArrayList<DemoDB> list = new ArrayList<>();
        Field[] fields = Site.class.getDeclaredFields();
        for (Field f : fields) {
            f.setAccessible(true);
            String filedName = f.getName();
            //1、获取属性上的指定类型的注释
            Column column = f.getAnnotation(Column.class);
            DemoDB demoDB = new DemoDB();
            if (f.getType() == String.class) {
                demoDB.setDbleixing("varchar");
            }
            if (f.getType() == double.class) {
                demoDB.setDbleixing("double");
            }
            if (f.getType() == int.class) {
                demoDB.setDbleixing("int");
            }
            if (f.getType() == long.class || f.getType() == Long.class) {
                demoDB.setDbleixing("bigint");
            }

            if (f.getType() == Date.class) {
                demoDB.setDbleixing("datetime");
            }
            if (f.getType() == Boolean.class) {
                demoDB.setDbleixing("tinyint");
            }
            //1、获取属性上的指定类型的注释
            if (column != null) {
                demoDB.setLeixing(f.getType().getName());
                if (StringUtil.isNoneBlank(column.name())) {
                    demoDB.setDbfield(column.name());
                } else {
                    demoDB.setDbfield(f.getName());
                }
            } else {
                demoDB.setLeixing(f.getType().getName());
                demoDB.setDbfield(f.getName());
            }
            list.add(demoDB);
        }
        EasyExcel.write(filename, DemoDB.class).sheet(4, "用户信息").doWrite(list);
    }


    /**
     * 写入
     *
     * @return
     * @throws ParseException
     */
    public static List<SyxUser> getAllNews2() throws ParseException {
        String sql = " SELECT h.`title`,f.`path`\n" +
                "FROM hngc_document_video v\n" +
                "LEFT JOIN hngc_document h\n" +
                "ON h.`id`=v.`id`\n" +
                "LEFT JOIN hngc_file f\n" +
                "ON v.`video_path`=f.`id`\n" +
                "WHERE h.`is_delete` IS FALSE;";
        List<SyxUser> list = new ArrayList<>();
        Statement stmt = null;
        Connection con = null;
        HashMap<Integer, Integer> map = new HashMap<>();
        String url = null;
        try {
            con = DBHelper.getConnection();
            PreparedStatement ps = con.prepareStatement(sql);
            stmt = (Statement) con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            SimpleDateFormat formattime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            if (rs != null) {
                while (rs.next()) {
                    SyxUser syxUser = new SyxUser();
                    syxUser.setName(rs.getString(1));
                    syxUser.setPhone("https://oss.hnzx.gov.cn/luohe" + rs.getString(2));
                    list.add(syxUser);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.close(con);
        }
        return list;
    }


    String cookie2 = "yfx_c_g_u_id_10000052=_ck22040609210418852366211033553; yfx_c_g_u_id_10000001=_ck22041109344910734577334470554; yfx_key_10000001=; yfx_f_l_v_t_10000001=f_t_1649640889075__r_t_1658888930674__v_t_1658888930674__r_c_9; yfx_f_l_v_t_10000052=f_t_1652683045481__r_t_1660267760060__v_t_1660267760060__r_c_12; sid=e15d91b0-c576-42d5-bb7c-e94a482c271f";


    public List<SyxUser> testSyxExcel() {
        List<SyxUser> lists = new ArrayList<>();
        // 读取excel
        EasyExcel.read("D:\\Desktop\\syx.xlsx", SyxUser.class, new AnalysisEventListener<SyxUser>() {
            int count = 0;

            @Override
            public void invoke(SyxUser demoData, AnalysisContext analysisContext) {
                lists.add(demoData);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                System.out.println("解析完成...");
            }
        }).sheet().doRead();
        return lists;
    }


    //批量上传视频
    @Test
    public void piliangAdd() throws IOException {
        List<SyxUser> syxUsers = testSyxExcel();
        System.out.println("数量为：" + syxUsers.size());
        int count = 0;
        for (SyxUser syxUser : syxUsers) {
            HashMap<String, String> map = new HashMap<>();
            map.put("name", syxUser.getName());
            map.put("materialType", "81");
            map.put("materialTypeName", "视频");
            map.put("author", "1");
            map.put("fileSize", "14989270");
            map.put("note", "暂无");
            map.put("siteId", "4620000395");
            map.put("thumbnail", syxUser.getPhone() + "?x-oss-process=video/snapshot,t_1000,m_fast");
            map.put("src", syxUser.getPhone());
            Response response = OkHttpUtils.newInstance().doPostAndHear("https://cms.hnzx.gov.cn/zw/material/add", map, cookie2);
            System.out.println(response.body().string() + " 计数：" + count++);

        }


    }


}
